﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CertificateDAL
/// </summary>
public class CertificateDAL
{
	public CertificateDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    

    public DataSet getCertificateInfoByCustId(String custId)
    {

        SqlConnection con = DBManager.getSqlConnection();

        String sql = " SELECT  (SELECT        fName + ' ' + lName AS custFullName"+
                     " FROM            CUSTOMER AS c"+
                     " WHERE        (custID = I.custID)) AS custFullName, I.custID,CONVERT(NVARCHAR(20),D.startDate,103) AS startDate, CONVERT(NVARCHAR(20),D.endDate,103) AS endDate,I.premiumPeriod, D.insuredID, D.planType,D.fName + ' ' + D.lName AS insuredFullName,D.fName, D.lName, I.category, I.productType, I.premiumPeriod, D.Premium, I.staffID, F.staffFName, F.staffLName,(F.staffFName +' '+F.staffLName) As staffFullName " +
                     " FROM  INSURANCE AS I INNER JOIN" +
	                 " INSURED AS D ON I.accountNo = D.accountNo INNER JOIN" +
		             " STAFF AS F ON I.staffID = F.staffID" +
                     " WHERE I.custID='" + custId + "'";

        SqlCommand cmd = new SqlCommand(sql, con);
      

        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();

        myAdapter.Fill(myDs);

        return myDs;
    }


    
 
}